/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bbdd.sqlite;

import bbdd.classes.RutaDet2;
import bbdd.classes.TRiesgo;
import bbdd.classes.TrutaMae;
import classes.Imagen;
import classes.Obstaculo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ConexionSqlLite {

    private Connection connection = null;
    private Statement statement = null;
    String archivo = null;

//Constructor de clase que se conecta a la base de datos SQLite 
    public ConexionSqlLite() {
    }

    public boolean connect(String text) {
        boolean resultado;
        resultado = false;
        archivo = text;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:" + text);

            resultado = true;
            Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.INFO, "Conectado a la base de datos SQLite [{0}]", text);

        } catch (ClassNotFoundException | SQLException e) {
            Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.SEVERE, null, e);
            resultado = false;
        }
        return resultado;
    }

    public boolean isConnected() {
        boolean resultado = false;
        try {
            if (connection != null && !connection.isClosed()) {
                resultado = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultado;
    }

    public List<TrutaMae> getRutas() {
        List<TrutaMae> resultado = null;
        if (isConnected()) {
            try {
                PreparedStatement ps = connection.prepareStatement("SELECT IDruta, fecha,descripcion from TrutaMae");
                ResultSet rs = ps.executeQuery();
                TrutaMae aux = null;
                resultado = new ArrayList<>();
                while (rs.next()) {
                    aux = new TrutaMae();
                    aux.setIdruta(rs.getInt("Idruta"));
                    aux.setFecha(new SimpleDateFormat("dd-MM-yyyy").parse(rs.getString("fecha")));
                    aux.setDescripcion(rs.getString("descripcion"));
                    resultado.add(aux);
                }

            } catch (ParseException | SQLException ex) {
                Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return resultado;

    }

    public List<RutaDet2> getTrayecto(Integer idruta) {
        List<RutaDet2> resultado = null;
        if (isConnected()) {
            try {
                PreparedStatement ps = connection.prepareStatement("SELECT Idruta, GPRMC, timeStamp, validSt, currentLat, nS, currentLon, eW, speed, trueCourse, dataStamp, CRC  "
                        + " from RUTADET2"
                        + " where Idruta = ?");
                ps.setInt(1, idruta);
                ResultSet rs = ps.executeQuery();
                RutaDet2 aux = null;
                resultado = new ArrayList<>();

                while (rs.next()) {
                    aux = new RutaDet2();
                    aux.setIdRuta(rs.getInt("Idruta"));
                    aux.setGprmc(rs.getString("GPRMC"));
                    Date fecha = new SimpleDateFormat("dd-MM-yyyy").parse(rs.getString("dataStamp"));
                    aux.setTimeStamp(Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(fecha) + " " + rs.getString("timeStamp")));
                    aux.setValidSt(rs.getString("validSt"));
                    aux.setCurrentLat(rs.getDouble("currentLat"));
                    aux.setNs(rs.getString("nS"));
                    aux.setCurrentLon(rs.getDouble("currentLon"));
                    aux.setEw(rs.getString("eW"));
                    aux.setSpeed(rs.getDouble("speed"));
                    aux.setTrueCourse(rs.getDouble("trueCourse"));
                    aux.setDataStamp(fecha);
                    aux.setCrc(rs.getString("CRC"));
                    resultado.add(aux);
                }

            } catch (ParseException | SQLException ex) {
                Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return resultado;
    }

    public List<TRiesgo> getRiesgos() {
        List<TRiesgo> resultado = null;
        if (isConnected()) {
            try {
                PreparedStatement ps = connection.prepareStatement("SELECT idRiesgo,Idruta,  timeStamp,  currentLat, nS, currentLon, eW,  dataStamp  "
                        + " from TRiesgo");


                ResultSet rs = ps.executeQuery();
                TRiesgo aux = null;
                resultado = new ArrayList<>();

                while (rs.next()) {
                    aux = new TRiesgo();

                    aux.setIdRiesgo(rs.getInt("idRiesgo"));
                    aux.setIdRuta(rs.getInt("Idruta"));
                    Date fecha = new SimpleDateFormat("dd-MM-yyyy").parse(rs.getString("dataStamp"));
                    aux.setTimeStamp(Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(fecha) + " " + rs.getString("timeStamp")));
                    aux.setCurrentLat(rs.getDouble("currentLat"));
                    aux.setNs(rs.getString("nS"));
                    aux.setCurrentLon(rs.getDouble("currentLon"));
                    aux.setEw(rs.getString("eW"));
                    aux.setDataStamp(fecha);
                    resultado.add(aux);
                }

            } catch (ParseException | SQLException ex) {
                Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return resultado;
    }

    public boolean insertarImagenes(List<Imagen> listaImagenes, Integer idRiesgo) {
        boolean resultado = false;
        int resultadoInt = 0;
        PreparedStatement ps;
        PreparedStatement ps2;
        PreparedStatement ps3;
        ResultSet rs;
        try {
            connection.setAutoCommit(false);

            ps = connection.prepareStatement("insert into Timagen(idimagen,idriesgo,path) "
                    + " select ifnull(max(idimagen)+1,1),?,? from timagen");

            ps2 = connection.prepareStatement("Select max(idimagen) as idimagen from timagen");

            ps3 = connection.prepareStatement("insert into Tobstaculo(Idobst, idRiesgo, pos_u, pos_v, ancho, largo,tipo,idimagen)"
                    + "Select ifnull(max(idobst)+1,1) as idobst,?,?,?,?,?,?,? from tobstaculo");

            for (Imagen aux : listaImagenes) {
                aux.setIdRiesgo(idRiesgo);

                ps.setInt(1, idRiesgo);
                ps.setString(2, aux.getPath());
                resultadoInt = resultadoInt + ps.executeUpdate();
                // obtenemos el id de la imagen
                rs = ps2.executeQuery();
                rs.next();
                aux.setIdImagen(rs.getInt("idimagen"));

                for (Obstaculo aux2 : aux.getListaObstaculos()) {
                    ps3.setInt(1, aux.getIdRiesgo());
                    ps3.setDouble(2, aux2.getPos_U());
                    ps3.setDouble(3, aux2.getPos_V());
                    ps3.setDouble(4, aux2.getAncho());
                    ps3.setDouble(5, aux2.getAlto());
                    ps3.setInt(6, aux2.getTipoObstaculo());
                    ps3.setInt(7, aux.getIdImagen());
                    ps3.executeUpdate();
                }
            }

            if (resultadoInt == listaImagenes.size()) {
                connection.commit();
                connection.setAutoCommit(true);
                resultado = true;
            }else{
                connection.rollback();
                connection.setAutoCommit(true);
            }
        } catch (SQLException ex) {
            try {
                connection.rollback();
                connection.setAutoCommit(true);
            } catch (SQLException ex1) {
                Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(ConexionSqlLite.class.getName()).log(Level.SEVERE, null, ex);

        }
        return resultado;
    }
}
